#-*- coding:utf-8 -*-
import sqlite3

class AssetDB(object):

    def __init__(self,db_path,table_name):
        super(AssetDB,self).__init__()

        self.db=sqlite3.connect(db_path)
        self.table_name=table_name
        self.cursor=self.db.cursor()

        self.createTable()


    def createTable(self):
        sql='create table if not exists {}(id integer primary key autoincrement,asset_name varchar(50),content text,tag varchar(200),name varchar(50),asset_path varchar(100))'.format(self.table_name)
        self.cursor.execute(sql)
        self.db.commit()
        # self.cursor.close()

    def addData(self,data):
        sql='insert into {}(asset_name,content,tag,name,asset_path)values("{}","{}","{}","{}","{}")'.format(self.table_name,data['asset_name'],data['content'],data['tag'],data['name'],data['asset_path'])
        self.cursor.execute(sql)
        self.db.commit()
        self.cursor.close()

    def searchData(self,search_type,tag):
        '''
        查询数据
        :param search_type: 查询类型,tag, asset_name, name
        :param tag: 具体查询内容
        :return:返回查询到的数据元组列表 eg :[(1,'老张的测试1','测试',"风 火 山 雷电",'"老张",'//192.168.1.247/effects/AssetsLib/a0'),(...)...]
        '''
        type_list=['id','asset_name','content','tag','name','asset_path']
        index=type_list.index(search_type)
        sql='select * from {}'.format(self.table_name)# where tag LIKE {},tag
        self.cursor.execute(sql)
        result=self.cursor.fetchall()
        # self.cursor.close()
        contain_list=[]
        for i in result:
            if i[index].find(tag)>=0:
                contain_list.append(i)
        return contain_list


    def testAdd(self):
        data={'asset_name':'老张的测试1','content':'测试','tag':"风 火 山 雷电",'name':"老张",'asset_path':'//192.168.1.247/effects/AssetsLib/a0'}
        data1 = {'asset_name': '老张的测试2', 'content': '测试2', 'tag': "风 火 山 雷电 爆炸", 'name': "老张",'asset_path':'//192.168.1.247/effects/AssetsLib/a1'}
        data2 = {'asset_name': '老张的测试3', 'content': '测试3', 'tag': "风 火 山 雷电 爆炸", 'name': "老张",'asset_path':'//192.168.1.247/effects/AssetsLib/a2'}
        data3 = {'asset_name': '老张的测试4', 'content': '测试4', 'tag': "粒子", 'name': "老张",'asset_path':'//192.168.1.247/effects/AssetsLib/a3'}
        data4 = {'asset_name': '老张的测试5', 'content': '测试5', 'tag': "布料", 'name': "老张",'asset_path':'//192.168.1.247/effects/AssetsLib/a4'}
        self.addData(data4)

    def testSearch(self):
        result=self.searchData('爆炸')
        print(result)

if __name__ == '__main__':
    db_path='//192.168.1.247/effects/AssetsLib/db'
    table_name='asset_lib'
    a=AssetDB(db_path,table_name)
    # a.createTable()
    # a.testAdd()
    print(a.testSearch())